package br.com.pucrs.tcc.bd;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;

import br.com.pucrs.tcc.ed.AreaInteresse;
import br.com.pucrs.tcc.ed.GrupoTrabalho;

public class AreaInteresseBD {

	private Session mSession;

	public AreaInteresseBD(Session session) {
		mSession = session;
	}

	public void insert(AreaInteresse area) {
		mSession.save(area);
		mSession.flush();
	}

	public void update(AreaInteresse area) {
		mSession.update(area);
		mSession.flush();
	}

	public void delete(AreaInteresse area) {
		mSession.delete(area);
		mSession.flush();
	}

	public AreaInteresse retrieve(AreaInteresse area) {
		AreaInteresse areaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append(" SELECT ai.* ");
		sql.append("   FROM areas_interesse ai ");
		sql.append("  WHERE ai.id = ? ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(AreaInteresse.class);
		query.setInteger(0, area.getId());

		areaRetorno = (AreaInteresse) query.uniqueResult();

		return areaRetorno;
	}

	public ArrayList<AreaInteresse> list(AreaInteresse area) {
		ArrayList<AreaInteresse> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append("     SELECT ai.* ");
		sql.append("       FROM areas_interesse ai ");
		sql.append("   ORDER BY ai.nome ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(AreaInteresse.class);

		listaRetorno = (ArrayList<AreaInteresse>) query.list();

		return listaRetorno;
	}

	public ArrayList<AreaInteresse> list(String idAreaInteresse) {
		ArrayList<AreaInteresse> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append("     SELECT ai.* ");
		sql.append("       FROM areas_interesse ai ");
		// sql.append("      WHERE ai.id = ? ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(AreaInteresse.class);
		query.setString(0, idAreaInteresse);

		listaRetorno = (ArrayList<AreaInteresse>) query.list();

		return listaRetorno;
	}// */

	public ArrayList<AreaInteresse> listaPorGrupo(Integer idGrupo) {
		ArrayList<AreaInteresse> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append("     SELECT a.* ");
		sql.append("       FROM areas_interesse a ");
		sql.append(" INNER JOIN grupos_trabalho_areas_interesse ga ");
		sql.append("         ON (a.id = ga.areas_interesse_id) ");
		sql.append("      WHERE ga.grupos_trabalho_id = ? ");
		sql.append("   ORDER BY ga.ordem_preferencia ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(AreaInteresse.class);
		query.setInteger(0, idGrupo);

		listaRetorno = (ArrayList<AreaInteresse>) query.list();

		return listaRetorno;
	}

	public ArrayList<AreaInteresse> listaNaoNoGrupo(Integer idGrupo) {
		ArrayList<AreaInteresse> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append("     SELECT a1.* ");
		sql.append("       FROM areas_interesse a1 ");
		sql.append("      WHERE a1.id NOT IN ( ");
		sql.append("    					       SELECT a2.id ");
		sql.append("       					  	     FROM areas_interesse a2 ");
		sql.append("                           INNER JOIN grupos_trabalho_areas_interesse ga ");
		sql.append("         					       ON (a2.id = ga.areas_interesse_id) ");
		sql.append("      						    WHERE ga.grupos_trabalho_id = ? ");
		sql.append("  	 					 ) ");
		sql.append("   ORDER BY a1.nome ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(AreaInteresse.class);
		query.setInteger(0, idGrupo);

		listaRetorno = (ArrayList<AreaInteresse>) query.list();

		return listaRetorno;
	}

	public ArrayList<AreaInteresse> listaPorProfessor(String matricula) {
		ArrayList<AreaInteresse> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append("     SELECT a.* ");
		sql.append("       FROM areas_interesse a ");
		sql.append(" INNER JOIN professores_areas_interesse pa ");
		sql.append("         ON (a.id = pa.areas_interesse_id) ");
		sql.append("      WHERE pa.professores_matricula = ? ");
		sql.append("   ORDER BY pa.ordem_preferencia ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(AreaInteresse.class);
		query.setString(0, matricula);

		listaRetorno = (ArrayList<AreaInteresse>) query.list();

		return listaRetorno;
	}

	public boolean verificaSelecionadaPorProfessor(Integer id) {
		ArrayList<AreaInteresse> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append("     SELECT a.* FROM areas_interesse a ");
		sql.append(" INNER JOIN professores_areas_interesse pa ");
		sql.append("         ON (a.id = pa.areas_interesse_id) ");
		sql.append("      WHERE a. id = ? ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(AreaInteresse.class);
		query.setInteger(0, id);

		listaRetorno = (ArrayList<AreaInteresse>) query.list();

		return listaRetorno.size() > 0;
	}

	public boolean verificaSelecionadaPorGrupo(Integer id) {
		ArrayList<AreaInteresse> listaRetorno = null;

		StringBuilder sql = new StringBuilder();

		sql.append("     SELECT a.* FROM areas_interesse a ");
		sql.append(" INNER JOIN grupos_trabalho_areas_interesse ga ");
		sql.append("         ON (a.id = ga.areas_interesse_id) ");
		sql.append("      WHERE a. id = ? ");

		SQLQuery query = mSession.createSQLQuery(sql.toString());
		query.addEntity(AreaInteresse.class);
		query.setInteger(0, id);

		listaRetorno = (ArrayList<AreaInteresse>) query.list();

		return listaRetorno.size() > 0;
	}

}
